package ru.timofeyco.justfeelbetter.db;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import ru.timofeyco.justfeelbetter.model.DayResult;
import ru.timofeyco.justfeelbetter.model.Drill;
import ru.timofeyco.justfeelbetter.model.DrillType;

public class JustFeelBetterDatabase {
	public static final int DB_VERSION = 1;
	public static final String DB_NAME = "justfeelfree";
		
	// drilltypes table info
	public static final String DRILL_TYPE_TABLE_NAME = "drilltypes";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_RATE = "rate";
	public static final String COLUMN_COLOR = "color";
	public static final String CREATE_DRILL_TYPE_TABLE = "create table " + DRILL_TYPE_TABLE_NAME + " ( _id integer primary key autoincrement, "
     	+ COLUMN_NAME + " TEXT, " + COLUMN_RATE + " INTEGER, " + COLUMN_COLOR + " INTEGER" + ")";
	
	// drill table info
	public static final String DRILL_TABLE_NAME = "drills";
	public static final String COLUMN_COUNT = "count";
	public static final String COLUMN_DRILL_TYPE = "idDrillType";
	public static final String COLUMN_DATE = "date";
	public static final String CREATE_DRILL_TABLE = "create table " + DRILL_TABLE_NAME + " ( _id integer primary key autoincrement, "
    	+ COLUMN_DRILL_TYPE + " INTEGER, " + COLUMN_DATE + " INTEGER, " + COLUMN_COUNT + " INTEGER" + ")";
	

	// dayresults table info
	public static final String DAY_RESULTS_TABLE_NAME = "dayresults";
	public static final String COLUMN_PRODUCTIVITY = "productivity";
	public static final String COLUMN_DAY_DATE = "date";
	public static final String CREATE_DAY_RESULTS_TABLE = "create table " + DAY_RESULTS_TABLE_NAME + " ( _id integer primary key autoincrement, "
    	+ COLUMN_PRODUCTIVITY + " INTEGER, " + COLUMN_DATE + " INTEGER" + ")";
	
	private static final int NOT_FOUND = -1;
	
	private SQLiteDatabase db; 
	DatabaseOpenHelper dbOpenHelper;
	
	public JustFeelBetterDatabase(Context context) {
		this.dbOpenHelper = new DatabaseOpenHelper(context);
		this.db = dbOpenHelper.getWritableDatabase();		
	}
	
	public void close() {
		this.db.close();
	}
	
	public boolean isDrillTypeExist(DrillType drillType) {
		return getDrillTypeByName(drillType.getName()) != NOT_FOUND;
	}
	
	public int getDrillTypeByName(String name) {
		Cursor cursor = db.query(DRILL_TYPE_TABLE_NAME, new String[] {"_id"}, COLUMN_NAME + "='" + name + "'", null, null, null, null);
		cursor.moveToFirst();
		int drillTypeId = (cursor.getCount() == 0) ? NOT_FOUND : cursor.getInt(0);
		return drillTypeId;
	}
	
	public DrillType[] getDrillTypes() {
		Cursor cursor = db.query(DRILL_TYPE_TABLE_NAME, new String[] {COLUMN_NAME, COLUMN_RATE, COLUMN_COLOR}, null, null, null, null, null);
		DrillType[] drillTypeArray = new DrillType[cursor.getCount()];
		cursor.moveToFirst();
		int i = 0;
        while (cursor.isAfterLast() == false) {
        	drillTypeArray[i] = new DrillType();
        	drillTypeArray[i].setName(cursor.getString(0));
        	drillTypeArray[i].setRate(cursor.getInt(1));
        	drillTypeArray[i].setColor(cursor.getInt(2));
            i++;
            cursor.moveToNext();
        }
		return drillTypeArray;
	}
	
	public Drill[] getDayDrills(Date date) {
		Date startDate = new Date(date.getYear(), date.getMonth(), date.getDate());
		Date finishDate = new Date(date.getYear(), date.getMonth(), date.getDate(), 23, 59, 59);
		/*Cursor cursor = db.query(DRILL_TABLE_NAME, new String[] {COLUMN_DRILL_TYPE, COLUMN_DATE, COLUMN_COUNT}, null, null, 
				null, null, null);*/
		/*		2011-06-17 00:00:00 2011-06-17 23:59:59*/
		/*Cursor cursor = db.rawQuery("SELECT idDrillType, date, count FROM drills WHERE date BETWEEN datetime('2011-06-17 00:00:00') AND datetime('2011-06-17 23:59:59')", 
				null);*/
		Cursor cursor = db.rawQuery("SELECT idDrillType, date, count FROM drills WHERE date BETWEEN '" + startDate.getTime() + "' AND '" + finishDate.getTime() + "'", null);
		cursor.moveToFirst();
		ArrayList<Drill> drills = new ArrayList<Drill>();
		HashMap<Long, DrillType> drillTypeMap = new HashMap<Long, DrillType>();
		int i = 0;
		while (cursor.isAfterLast() == false) {
			// get drill type
			long drillTypeId = cursor.getLong(0);
			DrillType drillType = null;			
			if (!drillTypeMap.containsKey(drillTypeId)) {
				// TODO: possible error (db.close)
				drillType = getDrillType(drillTypeId);
				drillTypeMap.put(drillTypeId, drillType);
			} else {
				drillType = drillTypeMap.get(drillTypeId);
			}
			if (drillType == null) {
				cursor.moveToNext();
				continue;
			}
			// fill drill by query result
			Drill newDrill = new Drill();
			newDrill.setType(drillType);
			newDrill.setCount(cursor.getInt(2));
			Date currDate = new Date(cursor.getLong(1));
			newDrill.setDate(currDate);
			drills.add(newDrill);
			
			i++;
			cursor.moveToNext();
		}
		Drill[] drillArr = new Drill[drills.size()];
		return drills.toArray(drillArr);
	}
	
	public DayResult getDayResult(Date date) {
		Cursor cursor = db.query(DAY_RESULTS_TABLE_NAME, new String[] {COLUMN_DAY_DATE, COLUMN_PRODUCTIVITY}, COLUMN_DAY_DATE + "=" + date.getTime(), 
				null, null, null, null);
		cursor.moveToFirst();
		DayResult dayResult = null;
		if (cursor.getCount() != 0) {
			dayResult = new DayResult(new Date(cursor.getLong(0)), cursor.getInt(1));
		}
		return dayResult;
	}
	
	public DrillType getDrillType(long id) {
		Cursor cursor = db.query(DRILL_TYPE_TABLE_NAME, new String[] {COLUMN_NAME, COLUMN_RATE, COLUMN_COLOR}, "_id=" + Long.toString(id), null, null, null, null);
		if (cursor.getCount() == 0) {
			return null;
		}
		cursor.moveToFirst();
		DrillType drillType = new DrillType(cursor.getString(0), cursor.getInt(1), cursor.getInt(2));
		return drillType;
	}
	
	public void insertDrillType(DrillType drillType) throws IOException {
		if (isDrillTypeExist(drillType)) {
			throw new IOException("DrillType: " + drillType.getName() + " alredy exists");			
		}
		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_NAME, drillType.getName());
		contentValues.put(COLUMN_RATE, drillType.getRate());
		contentValues.put(COLUMN_COLOR, drillType.getColor());
		db.insert(DRILL_TYPE_TABLE_NAME, null, contentValues);
	}
	
	public static void insertDrillType(DrillType drillType, SQLiteDatabase db) {		
		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_NAME, drillType.getName());
		contentValues.put(COLUMN_RATE, drillType.getRate());
		contentValues.put(COLUMN_COLOR, drillType.getColor());
		db.insert(DRILL_TYPE_TABLE_NAME, null, contentValues);		
	}
	
	public void insertDrill(Drill drill) throws IOException {
		int drillType = getDrillTypeByName(drill.getType().getName());
		if (drillType == NOT_FOUND) {
			throw new IOException("DrillType: " + drill.getType().getName() + " wasn't found.");
		}	
		SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_DRILL_TYPE, drillType);
		contentValues.put(COLUMN_DATE, drill.getDate().getTime());
		contentValues.put(COLUMN_COUNT, drill.getCount());
		db.insert(DRILL_TABLE_NAME, null, contentValues);
	}
	
	public boolean isDayResultExists(Date date) {
		DayResult dayResult = getDayResult(date);
		return dayResult != null;
	}
	
	public void insertDayResult(DayResult dayResult) throws IOException {
		if (isDayResultExists(dayResult.getDate())) {
			throw new IOException("Day result: " + dayResult.getDate().toString() + " already exists found.");
		}
		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_DATE, dayResult.getDate().getTime());
		contentValues.put(COLUMN_PRODUCTIVITY, dayResult.getProductivityPercent());
		db.insert(DAY_RESULTS_TABLE_NAME, null, contentValues);
	}
	
	public void updateDrillType(String oldDrillName, DrillType newDrillType) {
		Cursor cursor = db.rawQuery("SELECT _id FROM " + DRILL_TYPE_TABLE_NAME + " WHERE " + COLUMN_NAME + "='" + oldDrillName + "'", null);
		cursor.moveToFirst();
		int id = (cursor.getCount() == 0) ? NOT_FOUND : cursor.getInt(0);
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(COLUMN_NAME, newDrillType.getName());
		contentValues.put(COLUMN_RATE, newDrillType.getRate());
		contentValues.put(COLUMN_COLOR, newDrillType.getColor());
				
		db.update(DRILL_TYPE_TABLE_NAME, contentValues, "_id='" + id + "'", null);
	}
	
	public void updateDayResult(DayResult dayResult) {
		Cursor cursor = db.rawQuery("SELECT _id, date, productivity FROM dayresults WHERE date='" + dayResult.getDate().getTime() + "'", null);
		cursor.moveToFirst();
		int id = (cursor.getCount() == 0) ? NOT_FOUND : cursor.getInt(0);
		ContentValues contentValues = new ContentValues();
		
		contentValues.put(COLUMN_DATE, dayResult.getDate().getTime());
		contentValues.put(COLUMN_PRODUCTIVITY, dayResult.getProductivityPercent());
		db.update(DAY_RESULTS_TABLE_NAME, contentValues, "_id='" + id + "'", null);		
	}
	
	public void deleteDrillsByDay(Date date) {
		Date startDate = new Date(date.getYear(), date.getMonth(), date.getDate());
		Date finishDate = new Date(date.getYear(), date.getMonth(), date.getDate(), 23, 59, 59);
		
		db.delete(DRILL_TABLE_NAME, "date BETWEEN '" + startDate.getTime() + "' AND '" + finishDate.getTime() + "'", null);
		db.delete(DAY_RESULTS_TABLE_NAME, "date BETWEEN '" + startDate.getTime() + "' AND '" + finishDate.getTime() + "'", null);
	}
	
	public void deleteAllDrills() {
		db.delete(DRILL_TABLE_NAME, null, null);
		db.delete(DAY_RESULTS_TABLE_NAME , null, null);
	}

	public DayResult[] getMonthResult(Date date, int countDays) {
		Date startDate = new Date(date.getYear(), date.getMonth(), 1);
		Date finishDate = new Date(date.getYear(), date.getMonth(), countDays, 23, 59, 59);
		Cursor cursor = db.rawQuery("SELECT date, productivity FROM dayresults WHERE date BETWEEN '" + startDate.getTime() + "' AND '" + finishDate.getTime() + "'", null);
		cursor.moveToFirst();
		
		DayResult[] monthResult = new DayResult[countDays];		
		while (cursor.isAfterLast() == false) {
			Date currDate = new Date(cursor.getLong(0));
			int productivityPercent = cursor.getInt(1);
			DayResult dayResult = new DayResult(currDate, productivityPercent);
			
			monthResult[currDate.getDate() - 1] = dayResult;
			cursor.moveToNext();
		}
		
		return monthResult;		
	}

	public Drill[] getDrills() {		
		Cursor cursor = db.rawQuery("SELECT idDrillType, date, count FROM drills", null);
		cursor.moveToFirst();
		ArrayList<Drill> drills = new ArrayList<Drill>();
		HashMap<Long, DrillType> drillTypeMap = new HashMap<Long, DrillType>();
		int i = 0;
		while (cursor.isAfterLast() == false) {
			// get drill type
			long drillTypeId = cursor.getLong(0);
			DrillType drillType = null;			
			if (!drillTypeMap.containsKey(drillTypeId)) {
				drillType = getDrillType(drillTypeId);
				drillTypeMap.put(drillTypeId, drillType);
			} else {
				drillType = drillTypeMap.get(drillTypeId);
			}
			if (drillType == null) {
				cursor.moveToNext();
				continue;
			}
			// fill drill by query result
			Drill newDrill = new Drill();
			newDrill.setType(drillType);
			newDrill.setCount(cursor.getInt(2));
			Date currDate = new Date(cursor.getLong(1));
			newDrill.setDate(currDate);
			drills.add(newDrill);
			
			i++;
			cursor.moveToNext();
		}
		Drill[] drillArr = new Drill[drills.size()];
		return drills.toArray(drillArr);
	}
	
	public DayResult[] getDayResults() {
		Cursor cursor = db.rawQuery("SELECT date, productivity FROM dayresults", null);
		cursor.moveToFirst();
		
		ArrayList<DayResult> dayResults = new ArrayList<DayResult>();		
		while (cursor.isAfterLast() == false) {
			Date currDate = new Date(cursor.getLong(0));
			int productivityPercent = cursor.getInt(1);
			DayResult dayResult = new DayResult(currDate, productivityPercent);
			
			dayResults.add(dayResult);
			cursor.moveToNext();
		}
		DayResult[] dayResultArr = new DayResult[dayResults.size()];
		return dayResults.toArray(dayResultArr);	
		
	}
}
